<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
Class Mindex extends MY_Model
{
    public function __construct()
    {
        parent::__construct();
    }
    
    public function indexDB($provinceid,$districtid,$s)
    {
		$sql = '';
        if($this->session->userdata('level') != 0)
        {
            $sql = 'SELECT p.name, p.provinceid FROM province AS p INNER JOIN tbl_address AS a ON a.id_province = p.provinceid INNER JOIN address_user AS au ON au.id_address = a.id WHERE au.id_user = '.$this->session->userdata('id').' ';    
        }else{
            $sql = 'SELECT p.name, p.provinceid FROM province AS p INNER JOIN tbl_address AS a ON a.id_province = p.provinceid WHERE 1=1 ';
        }
		if( !in_array($provinceid,array('',null)) ){
			$sql .= ' AND p.provinceid = '.$provinceid.' ';
		}
		$sql .= ' GROUP BY p.provinceid ORDER BY p.name ASC';
		
		//die(''.$sql);
		
        $province = $this->db->query($sql)->result_object();
		
		//echo '<pre>';die(print_r($province));
		
        foreach($province AS $el=>$le)
        {
			$sql_1 = '';
			$sql_1 .= 'SELECT d.type, d.`name`, d.districtid FROM tbl_address AS a INNER JOIN district AS d ON a.id_district = d.districtid WHERE a.id_province = '.$le->provinceid.' ';
			if( !in_array($districtid,array('',null)) ){
				$sql_1 .= ' AND d.districtid = '.$districtid.' ';
			}
			$sql_1 .= ' GROUP BY d.districtid';
            $province[$el]->district = $this->db->query($sql_1)->result_object();
            foreach($province[$el]->district AS $eel=>$lle)
            {
				$sql_2 = '';
                if($this->session->userdata('level') != 0)
                {
                    $sql_2 .= 'SELECT ad.id, ad.address, ad.name FROM tbl_address AS ad INNER JOIN address_user AS au ON au.id_address = ad.id WHERE ad.id_district = '.$lle->districtid.' AND au.id_user = '.$this->session->userdata('id').' ';    
                }else{
                    $sql_2 .= 'SELECT ad.address, ad.id, ad.name FROM tbl_address as ad WHERE ad.id_district = '.$lle->districtid.' ';
                }
                if( !in_array($s,array('',null)) ){
					$sql_2 .= ' AND ( ad.address LIKE "%'.$s.'%" OR ad.name LIKE "%'.$s.'%" ) ';
				}
                $province[$el]->district[$eel]->address = $this->db->query($sql_2)->result_object();
                foreach($province[$el]->district[$eel]->address AS $ele=>$lee)
                {
                    $province[$el]->district[$eel]->address[$ele]->device = $this->db->query('SELECT td.*, uh.url_homepage FROM tbl_device AS td INNER JOIN url_homepage AS uh ON td.type = uh.id WHERE td.id_address = '.$lee->id)->result_object();
                }
            }
        }
		//echo '<pre>';die(print_r($province));
        return $province;
    }
    
    public function savePlace()
    {
        $arr = array(
                'id_province'   => $this->getParamInt('province'),
                'id_district'   => $this->getParamInt('district'),
                'address'       => $this->getParamString('address'),
                'created'       => date('Y-m-d H:i:s'),
                'lat'           => $this->getParamString('lat'),
                'long'          => $this->getParamString('long'),
                'name'          => $this->getParamString('name')
            );
        $this->db->insert('tbl_address', $arr);
        return true;
    }
    
    public function getPlaceSelect()
    {
        $data = $this->db->query('SELECT a.id, p.`name` AS pname, d.`name` AS dname, a.address FROM tbl_address AS a INNER JOIN district AS d ON d.districtid = a.id_district INNER JOIN province AS p ON p.provinceid = a.id_province ORDER BY a.address DESC')->result_array();
        
        $arr = array();
        foreach($data AS $el=>$le)
        {
            $arr[$le['id']] = $le['address'].' - '.$le['dname'].' - '.$le['pname'];
        }
        return $arr;
    }
    
    public function saveDevice($fileUpload)
    {
        $arr = array(
                'id_address'    => $this->getParamInt('place'),
                'namedevice'    => $this->getParamString('namedevice'),
                'mac'           => $this->getParamString('mac'),
				'nasid'           => $this->getParamString('nasid'),
                'created'       => date('Y-m-d H:i:s'),
                'linkupdate'    => $fileUpload,
                'version'       => $this->getParamString('version'),
				'active'        => $this->getParamInt('active'),
                'type'          => $this->getParamInt('types')
            ); 
        $this->db->insert('tbl_device', $arr);
        return true;
    }
    
    public function editDevice($id, $fileUpload)
    {
        $arr = array(
                'id_address'    => $this->getParamInt('place'),
                'namedevice'    => $this->getParamString('namedevice'),
                'mac'           => $this->getParamString('mac'),
				'nasid'         => $this->getParamString('nasid'),
                'version'       => $this->getParamString('version'),
				'active'        => $this->getParamInt('active'),
                'type'          => $this->getParamInt('types')
            );
        if($fileUpload != null)
        {
            $arr['linkupdate']  = $fileUpload;
        }
        $this->db->update('tbl_device', $arr, "id = ".$id);
        return true;
    }
    
    public function deleteDeviceInfo($id)
    {
        return $this->db->delete('tbl_device', "id = ".$id);
    }
    
    public function deletePlaceInfo($id)
    {
        return $this->db->delete('tbl_address', "id = ".$id);
    }
    
    public function editPlace($id)
    {
        $arr = array(
                'id_province'   => $this->getParamInt('province'),
                'id_district'   => $this->getParamInt('district'),
                'address'       => $this->getParamString('address'),
                'lat'           => $this->getParamString('lat'),
                'long'          => $this->getParamString('long'),
                'name'          => $this->getParamString('name')
            );
        $this->db->update('tbl_address', $arr, "id = ".$id);
        return true;
    }
    
    
    
    public function getPlace($number = 0,$offset = 0)
    {
        $limit = $offset ? intval($offset) : '0';
        
        $sql = "SELECT a.id, a.name, p.`name` AS pname, d.`name` AS dname, a.address FROM tbl_address AS a INNER JOIN district AS d ON d.districtid = a.id_district INNER JOIN province AS p ON p.provinceid = a.id_province ORDER BY a.id DESC";
        
        $count = $this->db->query($sql)->num_rows();
        
        $xdata = " LIMIT ".$limit.",".$number." ";
        
        $xs = $this->db->query($sql.$xdata)->result_object();
        return array('data' => $xs, 'count' => $count);
    }
    
    public function getDevice($number = 0,$offset = 0)
    {
        $limit = $offset ? intval($offset) : '0';
        
        $sql = "SELECT dt.`name` AS dname, p.`name` AS pname, d.`nasid`, d.`active`, d.id AS did,a.address, d.namedevice FROM tbl_device AS d INNER JOIN tbl_address AS a ON d.id_address = a.id INNER JOIN province AS p ON a.id_province = p.provinceid INNER JOIN district AS dt ON a.id_district = dt.districtid ORDER BY d.namedevice ASC";
        
        $count = $this->db->query($sql)->num_rows();
        
        $xdata = " LIMIT ".$limit.",".$number." ";
        
        $xs = $this->db->query($sql.$xdata)->result_object();
        return array('data' => $xs, 'count' => $count);
    }
    
    
    
    public function getPlaceInfo($id)
    {
        return $this->db->query('SELECT a.id, a.address, a.id_province, a.id_district, a.lat, a.long, a.name FROM tbl_address AS a WHERE a.id ='.$id)->row_object();
    }
    
    public function getDeviceInfo($id)
    {
        return $this->db->query('SELECT * FROM tbl_device WHERE id ='.$id)->row_object();
    }
    
    public function getListDistrict($id)
    {
        $data = $this->db->query('SELECT * FROM district WHERE provinceid = '.$id.' ORDER BY name ASC')->result_array();
        
        $arr = array();
        foreach($data AS $el=>$le)
        {
            $arr[$le['districtid']] = $le['type'].' '.$le['name'];
        }
        return $arr;
    }
    
    public function updatestatusDB()
    {
        $this->db->update('tbl_device', array('time_update' => time(), 'warning' => 0), "mac = '".$this->getParamString('id')."'");
    }
    
    public function warningSystemDB()
    {
        $data = $this->db->query('SELECT id_address, namedevice, mac, warning, id FROM tbl_device WHERE warning < 2 AND (('.time().' - time_update) >= 300) LIMIT 2')->result_object();
		foreach($data AS $el=>$le)
        {
			//echo 'SELECT u.username, u.mobile,ad.address,u.email,u.fullname FROM `user` AS u INNER JOIN address_user AS au ON au.id_user = u.id INNER JOIN tbl_address AS ad ON ad.id = au.id_address WHERE au.id_address = '.$le->id_address;
            $adata = $this->db->query('SELECT u.username, u.mobile,ad.address,u.email,u.fullname FROM `user` AS u INNER JOIN address_user AS au ON au.id_user = u.id INNER JOIN tbl_address AS ad ON ad.id = au.id_address WHERE au.id_address = '.$le->id_address)->row_object();
            //$mobile = substr($adata->mobile, 1);
            //echo "<pre>";
			//die(print_r($adata));
            $device = $le->namedevice . $le->mac;
            $addr = $adata->address;
            $content = $device.', tai dia chi '.$addr. "khong co internet";
			
			if($le->warning < 2)
			{
				//echo $adata->email;
			     /*
				$sendSMS = json_decode(file_get_contents('http://118.70.109.212:1234/?c='.$content.'&m='.$mobile.'&t_=1423463803'));
				if($sendSMS->status == 200)
				{
					$this->db->update('tbl_device', array('warning' => $le->warning + 1), "id = ".$le->id);
					echo "-1";
				}else{
					echo "-2";
				}
                */
                
                $config = array(
        						'protocol' => 'smtp',
        						'smtp_host' => 'mail.gvtechcom.vn',
        						'smtp_user' => 'info@gvtechcom.vn',
        						'smtp_pass' => 'abcde@123',
        						'mailtype' => 'html'
                            );
                $this->load->library('email',$config);
                $body    = "Chào bạn ".$adata->fullname."! <br/>".$content;
                $this->email->from('info@gvtechcom.vn', 'Cảnh báo hệ thống');
                $this->email->to($adata->email);    
                $this->email->subject('Cảnh báo hệ thống');
                $this->email->message($body);    
                if($this->email->send()){
					$this->db->update('tbl_device', array('warning' => $le->warning + 1), "id = ".$le->id);
					echo "-1";
                }
			}
        }
    }
    
    public function checkupdateDB($id)
    {
        $data = $this->db->query('SELECT version,linkupdate FROM tbl_device WHERE mac = "'.$id.'"')->row_object();
        return $data->version.'|'.site_url().'public/'.$data->linkupdate.'|'.$data->linkupdate;
    }
    
    public function getReportDb(){
        $data = $this->db->query('SELECT a.id,a.address,dis.name AS district,prov.name AS province FROM tbl_address AS a LEFT JOIN district AS dis ON dis.districtid = a.id_district LEFT JOIN province AS prov ON prov.provinceid = a.id_province')->result_object();
        
        foreach($data AS $key => $value){
            $query = $this->db->query('SELECT * FROM tbl_device WHERE id_address = '.$value->id);
            $data[$key]->count_device = $query->num_rows();
            $data[$key]->device = $query->result_object();
        }
        
        //echo "<pre>"; print_r($data); die();
        return $data;
    }
	
	public function geturlhomepage(){
		return $this->db->query('SELECT * FROM url_homepage WHERE active = 1')->result_object();
	}
	
	public function listprovince(){
		return $this->db->query('SELECT * FROM province WHERE 1=1')->result_object();
	}
	
	public function indexgetdistrict($id){
		$data = $this->db->query('SELECT * FROM district WHERE provinceid = '.$id.' ')->result_object();
		$html = '<option value="" selected="selected">Chọn quận huyện</option>';
		foreach ( $data AS $el=>$le ){
			$html .= '<option value="'.$le->districtid.'" >'.$le->name.'</option>';
		}
		return $html;
	}
	
	public function indexlistdistrict($pid,$did){
		$data = $this->db->query('SELECT * FROM district WHERE provinceid = '.$pid.' ')->result_object();
		$html = '<option value="">Chọn quận huyện</option>';
		foreach ( $data AS $el=>$le ){
			if( $le->districtid == $did ){
				$select = 'selected="selected"';
			}else{
				$select = '';
			}
			$html .= '<option value="'.$le->districtid.'" '.$select.' >'.$le->name.'</option>';
		}
		return $html;
	}
	
	public function devicedetail($id){
		$data = $this->db->query('SELECT td.*, ta.address FROM tbl_device as td inner join tbl_address as ta on ta.id = td.id_address WHERE td.id = '.$id.' ')->row_object();
		$data->comment = $this->db->query('SELECT * FROM comment WHERE device_id = '.$id.' ORDER BY created desc ')->result_object();
		return $data;
	}
	
	public function addCommentDb($id,$content){
		$arr = array(
				'device_id' => $id,
				'content' => $content,
				'admin_id' => $this->session->userdata('id'),
				'created' => date('Y/m/d H:i:s'),
				'active' => 1
			);
		$this->db->insert('comment',$arr);
		return 200;
	}
	
	public function ajaxupdatecomment(){
		$val = $this->getParamString('val');
		$val1 = explode('-',$val);
		$id = $val1[0]; 
		$active = $val1[1]; 
		if( $active == 1 ){ $active = 2;}else{ $active = 1; }
		$arr = array('active' => $active);
		$this->db->update('comment',$arr,'id = '.$id.' ');
		//die('111');
		return 200;
	}
	
}
